CREATE TABLE FileGroupAnalysisJob
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FileGroupID INT NOT NULL REFERENCES FileGroup(ID),
    TaskQueuedTime DATETIME2 NOT NULL,
    TaskPriority INT NOT NULL,
    ProcessingStartTime DATETIME2 NOT NULL,
    ProcessingEndTime DATETIME2 NOT NULL,
    ProcessingVersion INT NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupAnalysisJob_FileGroupID
ON FileGroupAnalysisJob(FileGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupAnalysisJob_TaskQueuedTime
ON FileGroupAnalysisJob(TaskQueuedTime ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupAnalysisJob_ProcessingStartTime
ON FileGroupAnalysisJob(ProcessingStartTime ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupAnalysisJob_ProcessingEndTime
ON FileGroupAnalysisJob(ProcessingEndTime ASC)
GO

DROP INDEX IX_DataOperationFailure_FileGroupID ON DataOperationFailure
GO

ALTER TABLE DataOperationFailure DROP COLUMN FileGroupID
GO

ALTER TABLE DataOperationFailure
ADD FileGroupAnalysisJobID INT REFERENCES FileGroupAnalysisJob(ID)
GO

CREATE NONCLUSTERED INDEX IX_DataOperationFailure_FileGroupAnalysisJobID
ON DataOperationFailure(FileGroupAnalysisJobID ASC)
GO

ALTER TABLE AnalysisTask
ADD TimeQueued DATETIME2 NOT NULL DEFAULT('0001-01-01 00:00:00')
GO

CREATE TABLE FileGroupAnalysisJobMeterConfiguration
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FileGroupAnalysisJobID INT NOT NULL REFERENCES FileGroupAnalysisJob(ID),
    MeterConfigurationID INT NOT NULL REFERENCES MeterConfiguration(ID)
)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupAnalysisJobMeterConfiguration_FileGroupAnalysisJobID
ON FileGroupAnalysisJobMeterConfiguration(FileGroupAnalysisJobID)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupAnalysisJobMeterConfiguration_MeterConfigurationID
ON FileGroupAnalysisJobMeterConfiguration(MeterConfigurationID)
GO

ALTER TABLE FaultSummary ADD PathEndSegmentID INT NOT NULL REFERENCES Asset(ID)
GO

ALTER TABLE DailyTrendingSummary ADD LastReceived DATETIME NULL
GO

CREATE NONCLUSTERED INDEX IX_DailyTrendingSummary_ChannelID_Date
ON DailyTrendingSummary(ChannelID ASC, Date ASC)
GO

ALTER TABLE AlarmGroup ADD Enabled BIT NOT NULL DEFAULT(1)
GO

DROP VIEW AlarmGroupView
GO

CREATE VIEW AlarmGroupView AS 
SELECT 
    AlarmGroup.ID,
    AlarmGroup.Name,
    AlarmSeverity.Name AlarmSeverity,
    CountStats.ChannelCount Channels,
    CountStats.MeterCount Meters,
    LastAlarm.StartTime LastAlarmStart,
    LastAlarm.EndTime LastAlarmEnd,
    LastAlarm.ChannelName LastChannel,
    LastAlarm.MeterName LastMeter,
    AlarmType.Description AS AlarmType,
    AlarmGroup.Enabled
FROM 
    AlarmGroup LEFT JOIN
    AlarmSeverity ON AlarmGroup.SeverityID = AlarmSeverity.ID LEFT JOIN
    AlarmType ON AlarmGroup.AlarmTypeID = AlarmType.ID OUTER APPLY
    (
        SELECT
            COUNT(DISTINCT Channel.ID) ChannelCount,
            COUNT(DISTINCT Channel.MeterID) MeterCount
        FROM
            Channel JOIN
            Series ON Series.ChannelID = Channel.ID JOIN
            Alarm ON Alarm.SeriesID = Series.ID
        WHERE Alarm.AlarmGroupID = AlarmGroup.ID
    ) CountStats OUTER APPLY
    (
        SELECT TOP 1
            LatestAlarmLog.StartTime,
            LatestAlarmLog.EndTime,
            Channel.Name ChannelName,
            Meter.Name MeterName
        FROM
            Alarm JOIN
            Series ON Alarm.SeriesID = Series.ID JOIN
            Channel ON Series.ChannelID = Channel.ID JOIN
            Meter ON Channel.MeterID = Meter.ID JOIN
            LatestAlarmLog ON LatestAlarmLog.AlarmID = Alarm.ID
        WHERE Alarm.AlarmGroupID = AlarmGroup.ID
        ORDER BY
            LatestAlarmLog.StartTime DESC,
            LatestAlarmLog.AlarmLogID DESC
    ) LastAlarm
GO

DROP VIEW ActiveAlarmView
GO

CREATE VIEW ActiveAlarmView AS
SELECT
    Alarm.ID AS AlarmID,
    Alarm.AlarmGroupID AS AlarmGroupID,
    AlarmGroup.AlarmTypeID AS AlarmTypeID,
    AlarmFactor.ID AS AlarmFactorID,
    AlarmFactor.SeverityID,
    Alarm.SeriesID AS SeriesID,
    AlarmFactor.Factor AS Value
FROM
    (
        SELECT
            AlarmFactor.ID,
            AlarmFactor.Factor,
            AlarmFactor.AlarmGroupID,
            AlarmFactor.SeverityID
        FROM
            AlarmFactor JOIN
            AlarmGroup ON AlarmFactor.AlarmGroupID = AlarmGroup.ID
        WHERE AlarmGroup.Enabled <> 0
        UNION
        SELECT
            NULL AS ID,
            1.0 AS Factor,
            AlarmGroup.ID AS AlarmGroupID,
            AlarmGroup.SeverityID
        FROM AlarmGroup
        WHERE AlarmGroup.Enabled <> 0
    ) AlarmFactor LEFT JOIN
    Alarm ON AlarmFactor.AlarmGroupID = alarm.AlarmGroupID LEFT JOIN
    AlarmGroup ON Alarm.AlarmGroupID = AlarmGroup.ID
GO

DROP VIEW [SEBrowser.EventSearchEventView]
GO

CREATE VIEW [dbo].[SEBrowser.EventSearchEventView] AS
SELECT
    Event.ID AS EventID,
    FORMAT(Event.StartTime,'MM/dd/yyyy <br> HH:mm:ss.fffffff') AS Time,
    Meter.AssetKey AS [Meter Key],
    Meter.Name AS [Meter],
    Meter.Alias AS [Meter Alias],
    Meter.ShortName AS [Meter ShortName],
    Meter.Make AS [Meter Make],
    Meter.Model AS [Meter Model],
    Meter.TimeZone AS [Meter TimeZone],
    Meter.Description AS [Meter Desc],
    Location.Name AS [Station],
    Location.LocationKey AS [Station Key],
    Location.ShortName AS [Station ShortName],
    Location.Alias AS [Station Alias],
    Location.Description AS [Station Desc],
    Asset.AssetName AS [Asset Name],
    AssetType.Name AS [Asset Type],
    Asset.VoltageKV AS [Nom Voltage (kV)],
    Asset.Description AS [Asset Desc]
FROM
    Event JOIN
    Meter ON Event.MeterID = Meter.ID JOIN 
    Location ON Meter.LocationID = Location.ID JOIN
    Asset ON Event.AssetID = Asset.ID JOIN
    AssetType ON Asset.AssetTypeID = AssetType.ID
GO

CREATE VIEW [DetailedMeter] AS
	SELECT
		DISTINCT
		Meter.ID,
		Meter.AssetKey,
		Meter.Name,
		Meter.Make,
		Meter.Model,
		Location.Name as Location,
		COUNT(DISTINCT MeterAsset.AssetID) as MappedAssets
	FROM
		Meter LEFT JOIN
		Location ON Meter.LocationID = Location.ID LEFT JOIN
		MeterAsset ON Meter.ID = MeterAsset.MeterID LEFT JOIN
		Asset ON MeterAsset.AssetID = Asset.ID LEFT JOIN
		Note ON Note.NoteTypeID = (SELECT ID FROM NoteType WHERE Name = 'Meter') AND Note.ReferenceTableID = Meter.ID
	GROUP BY
		Meter.ID,
		Meter.AssetKey,
		Meter.Name,
		Meter.Make,
		Meter.Model,
		Location.Name
GO

CREATE VIEW [DetailedAsset] AS
	SELECT
		DISTINCT
		Asset.ID,
		Asset.AssetKey,
		Asset.AssetName,
		Asset.VoltageKV,
		AssetType.Name as AssetType,
		COUNT(DISTINCT Meter.ID) as Meters,
		COUNT(DISTINCT Location.ID) as Locations
	FROM
		Asset JOIN
		AssetType ON Asset.AssetTypeID = AssetType.ID LEFT JOIN
		MeterAsset ON MeterAsset.AssetID = Asset.ID LEFT JOIN
		Meter ON MeterAsset.MeterID = Meter.ID LEFT JOIN
		AssetLocation ON AssetLocation.AssetID = Asset.ID LEFT JOIN
		Location ON AssetLocation.LocationID = Location.ID
	GROUP BY
		Asset.ID,
		Asset.AssetKey,
		Asset.AssetName,
		Asset.VoltageKV,
		AssetType.Name
GO

CREATE VIEW [DetailedLocation] AS
	SELECT
		DISTINCT
		l.ID,
		l.LocationKey,
		l.Name,
		l.Alias,
		l.ShortName,
		l.Latitude,
		l.Longitude,
		l.Description,
		COUNT(DISTINCT m.ID) as Meters,
		COUNT(DISTINCT al.AssetID) as Assets
	FROM
		Location as l LEFT JOIN
		Meter as m ON l.ID = m.LocationID LEFT JOIN
		AssetLocation as al ON l.ID = al.LocationID LEFT JOIN
		Asset as a ON al.AssetID = a.ID
	GROUP BY
		l.ID,
		l.LocationKey,
		l.Name,
		l.Alias,
		l.ShortName,
		l.Latitude,
		l.Longitude,
		l.Description
GO